package com.dangdi.dao;

import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;
import java.util.Map;

public interface Dao {
    @Select("select * from code_rfid")
    List<Map<String, Object>> test();

    @Insert("insert into code_rfid values(null,#{code},#{rfid},#{inserDatetime})")
    void insertBokk(@Param("code") String code, @Param("rfid") String rfid, @Param("inserDatetime") Date date);

    @Select("select * from code_rfid where code=#{code} and rfid=#{rfid}")
    List<Map<String, Object>> checkCodeAndRfid(@Param("code") String code, @Param("rfid") String rfid);

    @Select("select count(*) from code_rfid ")
    int getAllcount();

    @Select("select count(*) from code_rfid where date_format(datetime,'%y-%d-%m')=date_format(now(),'%y-%d-%m')")
    int getTodayCount();

    @Select("select * from code_rfid where code=#{code}")
    List<Map<String, Object>> checkCode(@Param("code") String code);

    @Select("select * from code_rfid where rfid=#{rfid}")
    List<Map<String, Object>> checkRfid(@Param("rfid") String rfid);

    //查询rfid重复
    @Select("select *,count(*) as count from code_rfid group by rfid having count>1")
    List<Map<String, Object>> checkRepeatRfid();

    //查询code重复
    @Select("select *,count(*) as count from code_rfid group by code having count>1")
    List<Map<String, Object>> checkRepeatCode();

    //查询条码格式错误的
    @Select("select * from code_rfid where length(code)!=7;")
    List<Map<String, Object>> checkCodeLength();

    //查询重复录书
    @Select("select *,count(*) as count from code_rfid group by code,rfid having count>1")
    List<Map<String, Object>> checkRepeat();

    //通过rfid纠正
    @Update("update code_rfid set code =#{code},datetime=#{inserDatetime} where rfid = #{rfid}")
    void UpdateByRfid(@Param("code") String code, @Param("rfid") String rfid, @Param("inserDatetime") Date date);

    //通过条码纠正
    @Update("update code_rfid set rfid =#{rfid},datetime=#{inserDatetime} where code = #{code}")
    void UpdateByCode(@Param("code") String code, @Param("rfid") String rfid, @Param("inserDatetime") Date date);

    @Delete(("delete from code_rfid where rfid=#{rfid}"))
    void delete(@Param("rfid") String rfid);

    //去重rfid
    @Delete("DELETE FROM code_rfid WHERE id NOT IN(SELECT * FROM(SELECT id FROM code_rfid GROUP BY rfid)AS b);")
    void quChongRfid();

    //去重code
    @Delete("DELETE FROM code_rfid WHERE id NOT IN(SELECT * FROM(SELECT id FROM code_rfid GROUP BY code)AS b);")
    void quChongCode();

    //去重
    @Delete("DELETE FROM code_rfid WHERE id NOT IN(SELECT * FROM(SELECT id FROM code_rfid GROUP BY code,rfid)AS b);")
    void quChong();

    //去重book_end表
    @Delete("DELETE FROM book_end WHERE book_id NOT IN(SELECT * FROM(SELECT book_id FROM book_end GROUP BY book_bar_code)AS b);")
    void quChongBookEnd();

    //查询读者（没有卡号的）
    @Select("select * from reader where reader_rfid is null or reader_rfid='' limit 1")
    Map<String, Object> getOneReaderForNotReaderCardID();

    //读者绑定卡号
    @Update("update reader set reader_rfid=#{reader_rfid},reader_state_id=#{reader_state_id},reader_roles_id=#{reader_roles_id},reader_password=#{reader_password},reader_loan_num=#{reader_loan_num},reader_employer=#{reader_employer},reader_register_date=date_format(now(),'%y-%d-%m %H:%i:%s'),reader_limited_time=#{reader_limited_time},reader_recharge_amount=#{reader_recharge_amount},reader_admin=#{reader_admin},reader_yujie_number=#{reader_yujie_number},reader_xujie_number=#{reader_xujie_number},reader_yujie_date=#{reader_yujie_date},reader_jieyue_date=#{reader_jieyue_date} where reader_id=#{reader_id}")
    int updateReaderByReader_id(Map reader);

    //获取所有读者数量
    @Select("select count(*) from reader")
    int AllReaderCount();

    @Select("select count(*) from reader where reader_rfid!=''")
    int updatedReaderCount();

    @Select("select count(*) from reader where reader_rfid=''")
    int NotUpdatedReaderCount();

    //获取角色列表
    @Select("select * from reader_roles")
    List<Map<String, Object>> readerRoleList();

    //状态列表
    @Select("select * from  reader_state")
    List<Map<String, Object>> readerStatusList();

    //查询m1卡号
    @Select("Select count(*) from reader where reader_rfid=#{reader_rfid}")
    int checkReader_rfid(@Param("reader_rfid") String reader_rfid);
    //新增了备注

    //
    @Insert("insert into bookshelf values(null,#{school},#{tag})")
    int insertExlUpload(@Param("school") String school, @Param("tag") String tag);

    //学校名称
    @Select("select distinct school from bookshelf")
    List<String> getSchoolList();

    //标签
    @Select("select tag  from bookshelf where  school=#{school} ORDER BY id")
    List<String> getTagListBySchool(@Param("school") String school);

    //获取书名
    @Select("select book_name from book_end where book_rfid=#{book_rfid}")
    String findBookNameByBook_rfid(@Param("book_rfid") String book_rfid);

    //获取条码
    @Select("select book_bar_code from book_end where book_rfid=#{book_rfid}")
    String findBookBarCodeByBook_rfid(@Param("book_rfid") String book_rfid);

    //添加书架和书
    @Insert("insert into bookShelfAndBook values(null,#{school},#{tag},#{book},#{cengRFID},#{code})")
    int insertBookShelfAndBook(@Param("school") String school, @Param("tag") String tag, @Param("book") String book, @Param("cengRFID") String cengRFID, @Param("code") String code);


    //删除
    @Delete("delete from bookShelfAndBook where book=#{book}")
    int deleteBookSheflByBookRfid(@Param("book") String book);

    @Select("select count(*) from bookShelfAndBook where book=#{book}")
    int checkBookShelfAndBook(@Param("book") String book);

    //绑定书架位
    @Insert("insert into bookLocation values(null,#{weizhima},#{rfid},#{zong},#{shujia},#{cengwei},#{lie},#{code},now())")
    int insertBookLocation(@Param("weizhima") String weizhima, @Param("rfid") String rfid, @Param("shujia") String shujia, @Param("cengwei") String cengwei, @Param("lie") String lie, @Param("code") String code, @Param("zong") String zong);

    //绑定书架位查找rfid
    @Select("select count(*) from bookLocation where rfid=#{rfid} and rfid !=''")
    int getBookLocationRfidCountByRfid(@Param("rfid") String rfid);

    //绑定书架位查找条码
    @Select("select count(*) from bookLocation where code=#{code}")
    int getBookLocationRfidCountByCode(@Param("code") String code);

    //绑定书架获取总数量
    @Select("select count(*) from bookLocation")
    int getBookLocationAllCount();

    //绑定书架获取今天绑定数量
    @Select("select count(*) from bookLocation where date_format(datetime,'%y-%d-%m')=date_format(now(),'%y-%d-%m')")
    int getBookLocationTodayCount();
    //图书上架操作查询书的位置


    @Select("select *,left(book_weizhi,instr(book_weizhi,'面')) as `left`,substr(left(book_weizhi,instr(book_weizhi,'层')),instr(book_weizhi,'面')+1) as `center`,substr(book_weizhi,instr(book_weizhi,'层')+1) as `right` from book_end where book_rfid=#{rfid}")
    Map<String, Object> book_end_infoByRfid(@Param("rfid") String rfid);


    //插入爬虫数据
    @Insert("insert into pachong values(null,#{manufacturer},#{model},#{engine_type},#{engine_power},#{product_total_lub_1},#{product_total_lub_2},#{product_total_transmission},#{title},#{productDetail},#{itenm})")
    int inserPachongdate(@Param("manufacturer") String manufacturer, @Param("model") String model, @Param("engine_type") String engine_type, @Param("engine_power") String engine_power, @Param("product_total_lub_1") String product_total_lub_1, @Param("product_total_lub_2") String product_total_lub_2, @Param("product_total_transmission") String product_total_transmission, @Param("productDetail") String productDetail, @Param("title") String title, @Param("itenm") String itenm);


    //获取爬虫数据
    @Select("Select * from pachong ")
    List<Map<String, Object>> getJson();

    @Select("select distinct manufacturer from pachong ")
    List<String> getManufacturer();

    @Select("select distinct model from pachong where manufacturer=#{manufacturer} ")
    List<String> getModel(@Param("manufacturer") String manufacturer);

    @Select("select distinct engine_type from  pachong where manufacturer=#{manufacturer} and model=#{model} ")
    List<String> getEngine_type(@Param("manufacturer") String s, @Param("model") String model);

    @Select("select distinct engine_power from  pachong where manufacturer=#{manufacturer} and model=#{model} ")
    List<String> getEngine_power(@Param("manufacturer") String s, @Param("model") String s1);

    //    @Select("select  id,biaoji1,biaoji2,product_total_lub_2,productDetail from  pachong where manufacturer=#{manufacturer} and model=#{model}  and engine_power=#{engine_power}")
    @Select("select  TRIM(substr(product_total_lub_1,locate('W',product_total_lub_1)-2,length(product_total_lub_1)))as nian1,TRIM(substr(product_total_lub_2,locate('W',product_total_lub_2)-2,length(product_total_lub_2)))as nian2 ,product_total_lub_2,product_total_lub_1,img1,img2 from  pachong where manufacturer=#{manufacturer} and model=#{model}  and engine_power=#{engine_power}")
    Map<String, Object> getDataByFourParam(@Param("manufacturer") String s, @Param("model") String s1, @Param("engine_power") String s3);
    @Insert("insert into pc values(null,#{name},#{s},#{salesName},#{s1},#{levelId},#{filling},#{high},#{lower})")
    void InsertPC(@Param("name") String name,@Param("s") String s,@Param("salesName") String salesName,@Param("s1") String s1,@Param("levelId") String levelId,@Param("filling") String filling, @Param("high") String high, @Param("lower") String lower);

    @Insert("insert into biansu values(null,#{paizi},#{chexing},#{pailiang},#{you})")
    int insertYou(@Param("paizi")String paizi,@Param("chexing")String chexing,@Param("pailiang")String pailiang,@Param("you")String you);
}
